-- Create Table

DROP TABLE Sales.Confirmation
GO
DROP PROCEDURE proc_AcceptConfirmation
GO
DROP ENDPOINT Confirmation_Endpoint
GO

CREATE TABLE Sales.Confirmation (
  OrderID int,
  Message nvarchar(80),
  XmlMessage xml)
GO

-- Create Stored Procedure

CREATE PROCEDURE dbo.proc_AcceptConfirmation @XmlMessage XML
AS
DECLARE @OrderID int
DECLARE @Message varchar(80)
SELECT @OrderID = @XmlMessage.value('(//@OrderID)[1]','int')
SELECT @Message = @XmlMessage.value('(//@Message)[1]','varchar(80)')
INSERT Sales.Confirmation(OrderID,Message,XmlMessage) VALUES (@OrderID,@Message,@XmlMessage)
GO

-- Test

DECLARE @XML XML
SET @XML = '<Order OrderID="42" Message="Foo" />'
EXECUTE dbo.proc_AcceptConfirmation @XML

SELECT * FROM Sales.Confirmation
GO

-- Expose Stored Procedure as a Web Service

CREATE ENDPOINT Confirmation_Endpoint STATE = STARTED
    AS HTTP (PATH = '/whidbeyrocks',
             AUTHENTICATION = (INTEGRATED),
             PORTS = (CLEAR),
             SITE = 'localhost') -- may have to change to hostname for biztalk. Example: vstsrc1
   FOR SOAP (WEBMETHOD 'SendConfirmation' 
               (NAME = 'AdventureWorks.dbo.proc_AcceptConfirmation'),
             BATCHES = DISABLED,
             WSDL    = DEFAULT,
             DATABASE = 'AdventureWorks',
             NAMESPACE = 'http://whidbeyrocks.net/',
             SCHEMA  = STANDARD)
GO

USE Master
GO

GRANT CONNECT ON ENDPOINT::Confirmation_Endpoint TO public
GO

USE AdventureWorks
GO

-- Test http://localhost/whidbeyrocks?wsdl

-- Test from .NET

SELECT * FROM Sales.Confirmation
